<?php

namespace App\Http\Models;

use DB;
use Auth;
use App\Exceptions\CustomException;
use App\Utils\QueryBuilder\Byval;
use Illuminate\Database\Eloquent\Model;
use App\Utils\QueryBuilder\QueryBuilder;
use App\Utils\QueryBuilder\ColumnRelateTable;
use App\Observers\HealthySampleObserver;
use Mrgoon\AliSms\AliSms;
use App\Http\Controllers\Common\MessageNoticeController;
class HealthySample extends Model
{
    //定义表
    protected $table = "health.healthy_samples";

    //定义主键
    protected $primaryKey = "healthy_collector_number";

    protected $keyType = "string";

    public $incrementing = false;

    protected $guarded = [];

    public static $lock_ok = 1;
    public static $bind_ok = 2;
    public static $send_ok = 3;
    public static $received_ok = 4;//确认收样
    public static $dna_sheet_ok = 5;//dna分配
    public static $dna_extract_ok = 6;//dna提取完成
    public static $library_sheet_ok = 7;//文库分配
    public static $library_prep_ok = 8;//文库完成
    public static $samplesheet_ok = 9;//samplesheet生成
    public static $report_verify_ok = 10;//报告审核
    public static $report_check_ok = 11;//报告签发

    protected  static  function boot()
    {
        parent::boot(); // TODO: Change the autogenerated stub
        self::observe(HealthySampleObserver::class);
    }

    /**
     * 样本列表
     * @param $request
     * @return QueryBuilder|mixed
     */
    public static function sampleList($request)
    {
        $byval = Byval::getQueryCondition($request, ['created_at' => 'DESC','healthy_collector_number'=>'DESC']);
        list($builder, $assoc) = self::baseBuilder();
        $builder = new QueryBuilder($builder, $byval, $assoc);
        return $builder;
    }

    /**
     * 报告列表
     * @param $byval
     * @return QueryBuilder|\Illuminate\Database\Query\Builder
     */
    public static function sampleReportsList($byval)
    {
        list($builder, $assoc) = self::baseBuilder();
        $builder = $builder->where('s.healthy_sample_status', '>=', HealthySample::$samplesheet_ok);
        $builder = new QueryBuilder($builder, $byval, $assoc);
        return $builder;
    }


    public static function baseBuilder()
    {
        $assoc = ColumnRelateTable::entitiesReflect([
            [new HealthySampleStatus(),'ss'],
            [new HealthyCombo(),'combo'],
            [new HealthyOrderChannel(),'hoc'],
            [new HealthyLibrarySheetDetail(),'lsd'],
            [new HealthySamplesheetDetail(),'hssd'],
            [new HealthySamplesheet(),'hss'],
            [new HealthySample(),'s'],
        ]);
        $builder = DB::table('health.healthy_samples as s')->select('s.healthy_collector_number',
            'ss.sample_status_name','combo.healthy_combo_name','hoc.order_channel_name',
            's.sample_name', 's.sample_sex', 's.sample_birth_date','s.sample_birth_place','s.nationality',
            's.ancestral_home','s.height', 's.weight','s.current_city', 's.current_address', 's.sample_courier_number',
            's.collector_bind_time','s.memo','lsd.inlet_port','s.collector_received_time','s.report_verify_name','s.collector_verify_time',
            's.healthy_sample_status', 's.created_at','s.is_stop','s.stop_id','s.if_result_exist','s.report_check_name','s.collector_check_time',
            's.order_channel', 's.collector_send_time','s.healthy_combo_id','s.collector_extract_sheet_time','hss.healthy_samplesheet_name',
            's.collector_check_time','s.healthy_combo_price','s.healthy_combo_id','s.report_pause','s.report_pause_time','hss.sample_sheet_id',
            's.report_pause_name')
            ->leftJoin('health.healthy_sample_status as ss', 'ss.healthy_sample_status', '=', 's.healthy_sample_status')
            ->leftJoin('health.healthy_combos as combo', 'combo.healthy_combo_id', '=', 's.healthy_combo_id')
            ->leftJoin('health.healthy_order_channels as hoc','hoc.healthy_order_channel','=','s.order_channel')
            ->leftJoin('health.healthy_library_sheet_details as lsd','lsd.healthy_collector_number','=','s.healthy_collector_number')
            ->leftJoin('health.healthy_samplesheet_details as hssd','hssd.healthy_collector_number','=','s.healthy_collector_number')
            ->leftJoin('health.healthy_samplesheets as hss','hss.sample_sheet_id','=','hssd.sample_sheet_id');
        return [$builder, $assoc];
    }

    /**
     * @param $healthyCollectorNumbers
     * @param $sampleStatus
     * @return mixed
     * @throws CustomException
     */
    public static function sampleStatusUpdate($healthyCollectorNumbers, $sampleStatus)
    {
        if(!is_array($healthyCollectorNumbers))
            $healthyCollectorNumbers = explode(',',$healthyCollectorNumbers);
        $currentTime = date('Y-m-d H:i:s');
        $username = Auth::guard('api')->user()->getAttribute('username');
        if($sampleStatus == HealthySample::$received_ok){
            $update = ['healthy_sample_status'=>$sampleStatus,'collector_received_time'=>$currentTime,'reveiced_name'=>$username];
        }elseif ($sampleStatus == HealthySample::$dna_sheet_ok) {
            $update = ['healthy_sample_status'=>$sampleStatus,'collector_extract_sheet_time'=>$currentTime,'extract_sheet_name'=>$username];

        }elseif ($sampleStatus == HealthySample::$dna_extract_ok) {
            $update = ['healthy_sample_status'=>$sampleStatus,'collector_extract_time'=>$currentTime,'extract_name'=>$username];

        }elseif ($sampleStatus == HealthySample::$library_sheet_ok) {
            $update = ['healthy_sample_status'=>$sampleStatus,'collector_lib_sheet_time'=>$currentTime,'lib_sheet_name'=>$username];

        }elseif ($sampleStatus == HealthySample::$library_prep_ok) {
            $update = ['healthy_sample_status'=>$sampleStatus,'collector_lib_prep_time'=>$currentTime,'lib_prep_name'=>$username];

        }elseif ($sampleStatus == HealthySample::$samplesheet_ok) {
            $update = ['healthy_sample_status'=>$sampleStatus,'collector_samplesheet_time'=>$currentTime,'samplesheet_name'=>$username];

        }elseif ($sampleStatus == HealthySample::$report_verify_ok) {
            $update = ['healthy_sample_status'=>$sampleStatus,'collector_verify_time'=>$currentTime,'report_verify_name'=>$username];

        }elseif ($sampleStatus == HealthySample::$report_check_ok) {
            $update = ['healthy_sample_status'=>$sampleStatus,'collector_check_time'=>$currentTime,'report_check_name'=>$username];
            HealthySampleItemsV3::updateIfShow($healthyCollectorNumbers);
        }
        else{
            $update = ['healthy_sample_status'=>$sampleStatus];
        }
        $MessageNoticeController =new MessageNoticeController();
        $MessageNoticeController->setMessageNoticeMany($healthyCollectorNumbers,$sampleStatus);
        $res = HealthySample::whereIn('healthy_collector_number', $healthyCollectorNumbers)
            ->where('report_pause',2)->where('is_stop',2)->update($update);
        return $res;
    }

    /**
     * 提取分配数据查询
     * @param $healthy_collector_numbers
     * @return array
     */
    public static function extractSheetCreate($healthy_collector_numbers)
    {
        $builder = DB::table('health.healthy_samples as s')->select('s.healthy_collector_number',
            's.sample_name', 's.sample_sex', 's.sample_birth_date', 's.nationality', 's.ancestral_home',
            's.sample_birth_place', 's.height', 's.weight', 'ss.sample_status_name')
            ->leftJoin('health.healthy_sample_status as ss', 'ss.healthy_sample_status', '=', 's.healthy_sample_status');
        $data = $builder->whereIn('healthy_collector_number', $healthy_collector_numbers)
            ->orderBy('healthy_collector_number','ASC')->get()
            ->map(function ($value) {
                return (array)$value;
            })->toArray();
        return $data;
    }

    /**
     * 建库分配数据查询
     * @param $healthy_collector_numbers
     * @return \Illuminate\Database\Query\Builder
     */
    public static function librarySheetCreate($healthy_collector_numbers)
    {
        $builder = DB::table('health.healthy_samples as s')->select('s.healthy_collector_number',
            'hed.healthy_qubit_conc','hed.healthy_sample_volume','hed.healthy_mal_volume','ss.sample_status_name',
            's.sample_name', 's.sample_sex', 's.sample_birth_date', 's.nationality', 's.ancestral_home',
            's.sample_birth_place', 's.height', 's.weight')
            ->leftJoin('health.healthy_sample_status as ss', 'ss.healthy_sample_status', '=', 's.healthy_sample_status')
            ->leftJoin('health.healthy_extract_dna as hed', 'hed.healthy_collector_number', '=', 's.healthy_collector_number')
            ->whereIn('s.healthy_collector_number', $healthy_collector_numbers)
            ->orderBy('s.healthy_collector_number','ASC');
        return $builder;
    }
    /**
     * @param $params
     * @return \Illuminate\Database\Query\Builder
     */
    public static function getNumANDIfShowBeforeReportChecked($params)
    {
        $healthy_collector_number = $params['healthy_collector_number'];
        $builder = DB::table('health.healthy_samples as s')->select('hfc.first_class_code',
            DB::raw('COUNT(hfc.first_class_code) as buy_num'))
            ->leftJoin('health.healthy_combo_details as cd', 'cd.healthy_combo_id', '=', 's.healthy_combo_id')
            ->leftJoin('health.healthy_codes_v3 as hc', 'hc.healthy_code', '=', 'cd.healthy_code')
            ->leftJoin('health.healthy_second_class_v3 as hsc', 'hsc.second_class_code', '=', 'hc.second_class_code')
            ->leftJoin('health.healthy_first_class_v3 as hfc', 'hfc.first_class_code', '=', 'hsc.first_class_code')
            ->where('s.healthy_collector_number', "$healthy_collector_number")
            ->where('cd.if_show', 1)
            ->groupBy('hfc.first_class_code');
        return $builder;
    }

    public static function sampleCheck($healthy_collector_number)
    {
        $healthySample = HealthySample::find($healthy_collector_number);
        if(empty($healthySample))
            throw new CustomException('样本不存在！');
        return $healthySample;

    }

}
